# coding=utf-8
from common.sqlOperate import *
from common.writeTxt import *
import os
from common.writeExcel import *
import datetime


def dealData(ob, idlist, start_t, end_t,t_seconds,thr_value):
    arr = []
    l_id = len(idlist)
    #print(l_id)
    arr.append('设备数：' + str(l_id) + '\n')
    for deviceid in idlist:
        #print(deviceid)
        arr.append(deviceid + " ")  # 获取对应的安全帽id
    arr.append("\n")
    id_good = []

    list_total = []
    rule_name = [f'测试个数：{l_id}', f'start:{start_t}', f'end:{end_t}']  # 信号消失的数据
    list_total.append(rule_name)
    com_list = ['id', '信号消失次数', '最大消失时长(s)','减去阈值的时长(s)', '最大的在线时间', '总消失时长']  # 基础数据
    list_total.append(com_list)

    for id in idlist:
        result = ob.reqVltBehavior(id, start_t, end_t)
        l_vlt = len(result)  # 单个id 的信号消失次数
        list_single = []
        # max_time2 = 0  # 最大的信号消失时长
        # max_time = 'max'  # 最大的在线时长
        # total_time = 0  # 总信号消失时长

        if l_vlt == 0:
            print(id)
            id_good.append(id)
        else:
            time_arr = []  # 统计两次信号消失的间隔时间
            time_arr2 = []  # 统计信号消失持续时间
            previous_row = None  # 定义一个上一行数据
            arr.append(id + '\n')
            for res in result:
                time_arr2.append(res[2])  # 信号消失持续时间写入
                if previous_row is not None:
                    previous_value = previous_row[1]
                    current_value = res[0]
                    value = current_value - previous_value  # 得到时间差表示  下一次违规的开始  减去上一次违规的结束时间
                    #value1 = previous_value - current_value
                    print(value)
                    #print(value1)
                else:
                    value = datetime.timedelta(seconds=0)  # 默认值给一个datatime 避免获取最大值时报type错误
                    # value_time = datetime.timedelta(seconds=0)  # 4s内的数据不算入违规总数
                    # print(value_time)

                time_arr.append(value)  # 持续有信号时间

                previous_row = res  # 将这行数据赋值，当作下一次的前一行数据
                arr.append(res[0].strftime("%Y-%m-%d %H:%M:%S") + " ")
                arr.append(res[1].strftime("%Y-%m-%d %H:%M:%S") + " ")  # 将datetime转成string
                arr.append(str(res[2]) + " ")
                arr.append(str(value))
                arr.append('\n')
            l = len(time_arr)

            max_time2 = max(time_arr2)  # 获取最大的信号消失时间
            d_max_time2 = max_time2 - thr_value  #最大信号消失时间减去阈值  表示真的消失时间

            if max_time2 <= thr_value:  # 给一个最大的信号消失时长  和阈值对比
                id_good.append(id)

                # 获取总的信号消失时长
            total_time = 0  # datetime.timedelta(seconds=0)

            for t in time_arr2:
                total_time += t  # 获取总的信号消失时长

            # print(max_time2)
            if l > 1:  # 求出最大的在线时间
                max_time = str(max(time_arr).total_seconds())  #最大在线时长
            else:
                #print(type(total_time),type(t_seconds))
                max_time = str(t_seconds - total_time)
               # max_time = 'max'



            arr.append(id + '信号消失次数：' + str(
                l_vlt) + "  最大信号消失时长：" + str(max_time2) + "s" + " 减去阈值的时长："+str(d_max_time2)+"s"+"  最大持续在线时间: " + max_time + " 总信号消失时长: " + str(
                total_time) + "\n\n")

            list_single.append(id)
            list_single.append(str(l_vlt))
            list_single.append(max_time2)  #最大信号消失时长
            list_single.append(d_max_time2) #最大的消失时长-阈值  软件消失时间
            list_single.append(max_time)  # 最大在线时间
            list_single.append(str(total_time))  # 信号消失总时长
            list_total.append(list_single)


    num = len(id_good)
    arr.append("未出现信号消失的id个数：" + str(num) + '   阈值：'+str(thr_value)+'s\n')
    for g in id_good:
        arr.append(g+' ')

    # 将两个数组转换为集合
    set_a = set(id_good)
    set_b = set(idlist)

    # 找到两个集合的差集
    r = set_b - set_a
    print(r)
    #r_s=", ".join(list(map(str, r))) #map转str
    r_s = ','.join("'{}'".format(item) for item in r)
    #print(type(r_s))

    arr.append("\n信号消失的设备：\n"+r_s)

    max_offline,offline_id,max_alloffline,alloffline_id = reqMaxValue(list_total)
    list_total.append([f'最大消失时长：{max_offline}——{offline_id}'])
    list_total.append([f'最大总消失时长：{max_alloffline}——{alloffline_id}'])
    list_total.append([f'未出现信号消失的id个数：{str(num)}'])
    list_total.append([f'阈值：{str(thr_value)}s'])
    list_total.append([f'信号消失的设备：{r_s}'])



    return arr,list_total

def reqMaxValue(list_total):
    offline_time = []
    alloffline_time = []
    deviceid = []
    for data in list_total[2:]:
        offline_time.append(data[2])
        alloffline_time.append(data[4])
        deviceid.append(data[0])

    if len(offline_time)!=0:
        max_offline = max(offline_time)
        offline_index = offline_time.index(max_offline)
        offline_id = deviceid[offline_index]
        max_alloffline = max(alloffline_time)
        alloffline_index = alloffline_time.index(max_alloffline)
        alloffline_id = deviceid[alloffline_index]
    else:
        max_offline,offline_id,max_alloffline,alloffline_id = 0,0,0,0

        print("该id正常")



    return max_offline,offline_id,max_alloffline,alloffline_id





if __name__ == '__main__':
    host = '192.168.3.138'
    # host = 'localhost'
    port = 3306
    user = 'testuser'
    password = 'test1234'
    db='gwza17'
    #db = 'gwza_refactory_local'
    #name = "警告型 — 禁止入内"
    # path = os.path.dirname(__file__)
    # print(path)

    ob = sqlOperate(host, port, user, password, db)
    ob.sqlConnect()
    #idslist = ['GW179E', 'GW47E2', 'GW44C9', 'GW1923']
    df = pd.read_excel('./config/data.xls', sheet_name='test', header=0)
    ids = df.values
    idslist = []
    for v in ids:
        idslist.append(v[0])

    t1 = '2024-08-07 10:00:00'  # 定义初始时间
    t_minutes = 240  # 持续观察的分钟数  min单位
    t_seconds = t_minutes*60

    # 将字符串转换为datetime对象
    dt1 = datetime.datetime.strptime(t1, '%Y-%m-%d %H:%M:%S')

    # 创建一个timedelta对象，表示时间差
    time_delta = datetime.timedelta(minutes=t_minutes)

    # 将时间差加到原始时间上
    t2 = dt1 + time_delta  #结束时间

    # 格式化输出结果
    print(t2.strftime('%Y-%m-%d %H:%M:%S'))

    thr_value = 0  #信号消失阈值  20s表示离线时间点  再加4s表示离线4s内表示可忽略
    arr,list_total = dealData(ob, idslist, t1, t2,t_seconds,thr_value)
    #max_offline, max_alloffline = reqMaxValue(list_total)

    name = '信号消失'
    wt = writeTxt()
    wt.write_to_txt(name, arr)

    filename = 'result_behavior.xlsx'
    we = writeExcel(filename)
    ws = we.create_excel_sheet()
    #输出 不好的数据的id
    we.append_to_excel(list_total, ws)
    we.save_to_excel()

    ob.closeConnection()



# df = pd.read_excel('./config/data.xls', sheet_name='test', header=0)
